import pandas as pd

import xlrd
from xlrd import XLRDError
import xlutils.copy

# 要填入月份对应的行字符串
month = '1月份'
# 读取11月工资单（文件对应操作系统的绝对路径）
salaryPerMonthDF = pd.read_excel(
    '/Users/jizhaolun/Desktop/projects/python-test/com/jzl/excel/forxulei/files/梅溪小学11月份工资单（好）.xls', sheet_name='教师')


# print(salaryPerMonth)

def setOutCell(outSheet, col, row, value):
    """ Change cell value without changing formatting. """

    def _getOutCell(outSheet, colIndex, rowIndex):
        """ HACK: Extract the internal xlwt cell representation. """
        row = outSheet._Worksheet__rows.get(rowIndex)
        if not row: return None

        cell = row._Row__cells.get(colIndex)
        return cell

    # HACK to retain cell style.
    previousCell = _getOutCell(outSheet, col, row)
    # END HACK, PART I

    outSheet.write(row, col, value)

    # HACK, PART II
    if previousCell:
        newCell = _getOutCell(outSheet, col, row)
        if newCell:
            newCell.xf_idx = previousCell.xf_idx


# 遍历11月工资单获取所有人名（用以在汇总表中寻找sheet页）
for i in salaryPerMonthDF.index:
    # print(type(salaryPerMonth.loc[i].values[0]))
    teacherData = salaryPerMonthDF.loc[i]
    if isinstance(teacherData.values[0], str):
        # print(teacherData.values[0].strip('\n'))
        try:
            # 获取【梅溪小学（好）.xls】中对应teacherName的sheet页
            salaryToFillSheetDF = pd.read_excel(
                '/Users/jizhaolun/Desktop/projects/python-test/com/jzl/excel/forxulei/files/梅溪小学（好）.xls',
                sheet_name=teacherData.values[0].strip('\n'))
            # print(salaryToFillSheetDF)
            # 获取要填入月份对应的行
            for j in salaryToFillSheetDF.index:
                toFillData = salaryToFillSheetDF.loc[j]
                # print(toFillData.values[1])
                if month == toFillData.values[1]:
                    # 替换数据
                    toFillData.values[2:21] = teacherData.values[3:22]
                    print(toFillData.values[2:21])
                    # 写文件(无法保存格式，废弃)
                    # writer = pd.ExcelWriter('/Users/jizhaolun/Desktop/projects/python-test/com/jzl/excel/forxulei/files/梅溪小学（好）.xlsx', mode='a', engine="openpyxl")
                    # salaryToFillSheetDF.to_excel(writer,  sheet_name=teacherData.values[0].strip('\n'))
                    # writer.save()
                    # writer.close()

                    # 带格式地写文件
                    rb = xlrd.open_workbook(
                        '/Users/jizhaolun/Desktop/projects/python-test/com/jzl/excel/forxulei/files/梅溪小学（好）.xls',
                        formatting_info=True)
                    wb = xlutils.copy.copy(rb)
                    # print(toFillData)
                    outSheet = wb.get_sheet(teacherData.values[0].strip('\n'))
                    for k in range(2, 22):
                        # 判断逻辑去除【预发】选项
                        if k==11:
                            continue
                        elif k<11:
                            setOutCell(outSheet, k, j + 1, teacherData.values[k + 1])
                        else:
                            setOutCell(outSheet, k, j + 1, teacherData.values[k])

                        print('k = ', k)
                    wb.save('/Users/jizhaolun/Desktop/projects/python-test/com/jzl/excel/forxulei/files/梅溪小学（好）.xls')

        except (XLRDError):
            print("找不到名为" + teacherData.values[0] + "的老师")
        except Exception as e:
            print("寻找" + teacherData.values[0] + "老师的数据时发生未知异常:\n" + repr(e))

        # print(salaryToFillSheetDF)
